package com.supermarket.dao;

import java.sql.*;
import java.util.ArrayList;
import com.supermarket.entity.CommodityEntity;

public class CommodityDao {

    private static String DB_URL = "";
    private static String DB_USER = "";
    private static String DB_PASSWORD = "";

    public static void MySQLSettingForCommodity(String DB_URL1,String DB_USER1,String DB_PASSWORD1){
        DB_URL = DB_URL1;
        DB_USER = DB_USER1;
        DB_PASSWORD = DB_PASSWORD1;
    }
    //展示全部
    public ArrayList<CommodityEntity> all() {
        String query = "SELECT * FROM commodity";
        return executeQuery(query);
    }

    //根据id查询
    public CommodityEntity getById(Long commodityId) {
        if (commodityId == null) {
            return null;
        }
        String query = "SELECT * FROM commodity WHERE id = ?";
        ArrayList<CommodityEntity> results = executeQuery(query, commodityId);
        return results.isEmpty() ? null : results.get(0);
    }

    //插入
    public int insert(CommodityEntity commodity) {
        String query = "INSERT INTO commodity (name, number, enterprice, sales, productiondate, guaranteeperiod, supplier, createtime) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        return executeUpdate(query, commodity.getName(), commodity.getNumber(), commodity.getEnter_price(), commodity.getSales(), commodity.getProduction_data(), commodity.getGuarantee_period(), commodity.getSupplier(), commodity.getCreate_time());
    }

    //修改
    public int update(CommodityEntity commodity) {
        String query = "UPDATE commodity SET name = ?, number = ?, enterprice = ?, sales = ?, productiondate = ?, guaranteeperiod = ?, supplier = ?, createtime = ? WHERE id = ?";
        return executeUpdate(query, commodity.getName(), commodity.getNumber(), commodity.getEnter_price(), commodity.getSales(), commodity.getProduction_data(), commodity.getGuarantee_period(), commodity.getSupplier(), commodity.getCreate_time(), commodity.getId());
    }

    //删除
    public int del(Long id) {
        if (id == null) {
            return 0;
        }
        String query = "DELETE FROM commodity WHERE id = ?";
        return executeUpdate(query, id);
    }

    //新增出库记录
    public int addOutboundRecord(CommodityEntity commodity, Long quantity) {
        String query = "INSERT INTO outbound_goods (name, number, enter_price, sales, production_date, guarantee_period, supplier, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())";
        return executeUpdate(query, commodity.getName(), quantity, commodity.getEnter_price(), commodity.getSales(), commodity.getProduction_data(), commodity.getGuarantee_period(), commodity.getSupplier());
    }

    //查询所有出库记录
    public ArrayList<CommodityEntity> allOutbound() {
        String query = "SELECT * FROM outbound_goods";
        return executeQuery(query);
    }

    private ArrayList<CommodityEntity> executeQuery(String query, Object... params) {
        ArrayList<CommodityEntity> commodities = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement statement = prepareStatement(connection, query, params);
             ResultSet resultSet = statement.executeQuery()) {

            while (resultSet.next()) {
                Long id = resultSet.getLong("id");
                String name = resultSet.getString("name");
                Long number = resultSet.getLong("number");
                Long enter_price = resultSet.getLong("enterprice");
                Long sales = resultSet.getLong("sales");
                String production_date = resultSet.getString("productiondate");
                Long guarantee_period = resultSet.getLong("guaranteeperiod");
                String supplier = resultSet.getString("supplier");
                String create_time = resultSet.getString("createtime");
                CommodityEntity commodity = new CommodityEntity(id, name, number, enter_price, sales, production_date, guarantee_period, supplier, create_time);
                commodities.add(commodity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return commodities;
    }

    private int executeUpdate(String query, Object... params) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement statement = prepareStatement(connection, query, params)) {

            return statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    private PreparedStatement prepareStatement(Connection connection, String query, Object... params) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(query);
        for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
        }
        return statement;
    }
}